package com.kaikeba.dao.impl;

import com.kaikeba.bean.Courier;
import com.kaikeba.bean.User;
import com.kaikeba.dao.CourierDao;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CourierDaoImpl implements CourierDao {
    /**
     * 用户返回控制台中所需的数据
     * {（快递员人數）c_sum：xxx,（日注册量）c_day：xxx}
     *
     * @return
     */
    @Override
    public Map<String, Integer> console() {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        Map<String,Integer> map = new HashMap<>();
        try {
            state = conn.prepareStatement("select \n" +
                    "COUNT(id) c_sum,COUNT(TO_DAYS(registertime)=TO_DAYS(NOW()) or null) c_day\n" +
                    "from courier");
            resultSet = state.executeQuery();
            while (resultSet.next()){
                int c_sum = resultSet.getInt("c_sum");
                int c_day = resultSet.getInt("c_day");
                map.put("c_sum",c_sum);
                map.put("c_day",c_day);
            }
            return map;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,resultSet);
        }
        return null;
    }

    /**
     * 查找全部快递并根据参数判断是否分页
     *
     * @param limit      是否分页 true（默认）分页  false 不分页
     * @param offset     起始索引
     * @param pageNumber 单页显示数量
     * @return
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        List<Courier> list = new ArrayList<>();
        try {
            if (limit){
                //分页
                state = conn.prepareStatement("select * from courier limit ?,?");
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                //不分页
                state = conn.prepareStatement("select * from courier");
            }
            resultSet = state.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String userName = resultSet.getString("username");
                String userPhone = resultSet.getString("userphone");
                String password = resultSet.getString("password");
                String identityNumber = resultSet.getString("identitynumber");
                int passnumber = resultSet.getInt("passnumber");
                Timestamp registerTime = resultSet.getTimestamp("registertime");
                Timestamp loginTime = resultSet.getTimestamp("logintime");
                Courier courier = new Courier(id,userName,userPhone,identityNumber,password,registerTime,loginTime,pageNumber);
                courier.setPassNumber(passnumber);
                list.add(courier);
            }
            return list;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,resultSet);
        }

        return null;
    }

    /**
     * 根据用户电话号码查询快递信息
     *
     * @param userPhone 用户电话号码
     * @return
     */
    @Override
    public List<Courier> findByCourierPhone(String userPhone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        List<Courier> list = new ArrayList<>();
        try {
            state = conn.prepareStatement("select * from courier where userphone=?");
            state.setString(1,userPhone);
            resultSet = state.executeQuery();

            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String userName = resultSet.getString("username");
                String userphone = resultSet.getString("userphone");
                String identityNumber = resultSet.getString("identitynumber");
                String password = resultSet.getString("password");
                Courier courier = new Courier(id,userName,userPhone,identityNumber,password);
                list.add(courier);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,resultSet);
        }

        return null;
    }

    /**
     * 录入
     *
     * @param c 录入的快递对象
     * @return 录入的结果，true表示成功，false表示失败
     */
    @Override
    public boolean insert(Courier c) {

        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            String userName = c.getUserName();
            String userPhone = c.getUserPhone();
            String identityNumber = c.getIdentityNumber();
            String password = c.getPassword();
            int passNumber = c.getPassNumber();

            state = conn.prepareStatement("insert into courier values (null ,?,?,?,?,now(),null,? )");
            state.setString(1,userName);
            state.setString(2,userPhone);
            state.setString(3,identityNumber);
            state.setString(4,password);
            state.setInt(5,passNumber);
            int i = state.executeUpdate();
            return i>0;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用户修改
     *
     * @param id         要修改的快递员id
     * @param newCourier 新的快递对象（userName，userPhone,identityNumber,password）
     * @return 修改的结果，true表示成功，false表示失败
     */
    @Override
    public boolean update(int id, Courier newCourier) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            String userName = newCourier.getUserName();
            String userPhone = newCourier.getUserPhone();
            String identityNumber = newCourier.getIdentityNumber();
            String password = newCourier.getPassword();

            state = conn.prepareStatement("update courier set username=?,userphone=?,identitynumber=?,password=?\n" +
                    "where id =?;");
            state.setString(1,userName);
            state.setString(2,userPhone);
            state.setString(3,identityNumber);
            state.setString(4,password);
            state.setInt(5,id);

            int i = state.executeUpdate();
            return i>0;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 删除
     *
     * @param id 需要删除的用户id
     * @return
     */
    @Override
    public boolean delete(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            state = conn.prepareStatement("delete from courier where id=? ");
            state.setInt(1,id);
            int i = state.executeUpdate();
            return i>0;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }



    /**
     * 用于微信端用户的登录
     *
     * @param userPhone 用户手机号码
     * @param password  密码
     * @return
     */
    @Override
    public Courier login(String userPhone, String password) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;

        try {
            state = conn.prepareStatement("select * from courier where userphone=? and password=?");
            state.setString(1,userPhone);
            state.setString(2,password);
            resultSet = state.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String userName = resultSet.getString("username");
                String userPhone1 = resultSet.getString("userphone");
                String password1 = resultSet.getString("password");
                int passnumber = resultSet.getInt("passnumber");
                String identityNumber = resultSet.getString("identitynumber");
                Timestamp registerTime = resultSet.getTimestamp("registertime");
                Timestamp loginTime = resultSet.getTimestamp("logintime");
                Courier courier = new Courier(id,userName,userPhone,identityNumber,password,registerTime,loginTime,passnumber);
                return courier;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,resultSet);
        }
        return null;
    }
}
